package com.mzss.excel.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excel 导出工具类。用户日常，excel 导出功能，采用的是poi 3.16版本
 *
 * @author panqingcui
 * @create 2017-09-11 10:38
 */
public class ExcelUtil {
    private static final Logger LOGGER = LogManager.getLogger(ExcelUtil.class);

    /**
     * 导出excel
     * @param objects 内容的二维数组 Object[][]:行 ：列（单元格）
     * @param title 标题
     * @param sheetname sheet name
     * @param filename export file name
     */
    public void export(Object[][] objects, String[] title, String sheetname, String filename) {
        // first book
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // second sheet
        XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetname);
        // 行号
        int rowNum = 0;
        for (Object[] datatype : objects) {
            // 创建行
            Row row = xssfSheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : datatype) {
                // 单元格
                Cell cell = row.createCell(colNum++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
        }
        try {
            FileOutputStream outputStream = new FileOutputStream(filename);
            xssfWorkbook.write(outputStream);
            xssfWorkbook.close();
        } catch (FileNotFoundException e) {
            LOGGER.error("not found file:{}", filename);
        } catch (IOException e) {
            LOGGER.error("export file faild:{}", filename);
        }
    }

    /**
     * 读取excel
     * @param filename
     */
    public void read(String filename) {
        // first
        try {
            FileInputStream fileInputStream = new FileInputStream(filename);
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            // sheet的个数
            int numOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numOfSheets; i++) {
                // 获取sheet
                Sheet sheet = workbook.getSheetAt(i);
                //
                Iterator<Row> iterator = sheet.iterator();
                while (iterator.hasNext()) {
                    Row currentRow = iterator.next();
                    Iterator<Cell> cellIterator = currentRow.iterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        if (cell.getCellTypeEnum() == CellType.STRING) {
                            LOGGER.info(cell.getStringCellValue());
                        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            LOGGER.info(cell.getNumericCellValue());
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {
            LOGGER.error("无法找到文件{}", filename);
        } catch (IOException e) {
            LOGGER.error("读取excel异常", e);
        }
    }
}
